<?php

namespace App\Admin\Controllers;

use App\Http\Controllers\Controller;

use App\Models\ChannelCateGoodsModel;
use function foo\func;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;

class SqlController extends BaseController
{

    public function __construct()
    {
        set_time_limit(100);
    }

    //老数据表attr_val属性批量处理到goods_sku
    public function goodsSkuSql(){
//        die('禁用');

        $start_time=microtime(true);

        $sql="insert into goods_sku(goods_id,sku,goods_price,goods_official_price,goods_purchase_price)
            select g.goods_id,v.id,if(v.attr_price>0,v.attr_price,g.goods_price) as price,
            if(v.goods_official_price>0,v.goods_official_price,g.goods_official_price) as goods_official_price,g.goods_purchase_price
            from goods as g
            inner join attr_type as t ON t.goods_id=g.goods_id
            inner join attr_val as v on t.attr_id=v.attr_id  where g.goods_id in(
            select goods_id from (select g.goods_id,count(*) as num from goods as g INNER JOIN attr_type as t on g.goods_id=t.goods_id GROUP BY g.goods_id  HAVING num=1) as tmp
            ";

        DB::beginTransaction();
        try{
            $res=DB::select($sql);
            DB::commit();
        }catch (\Exception $e){
            DB::rollBack();
            die($e->getMessage());
        }
        echo '成功: 运行时长：'.(microtime(true)-$start_time);
    }


    //买断金
    public function buyMoney($cate_id,$goods_official_price,$goods_price){
        $money=0;
        if (in_array($cate_id, [286])) {
            $money = number_deal($goods_official_price * 1.1 - $goods_price, 2);
            $money= abs($money);
        } elseif (in_array($cate_id, [287, 298, 299])) {
            //298苹果电脑 299联想电脑
            $money = number_deal($goods_official_price-$goods_price, 2);
            $money = abs($money);
        } else {
            $money = 0;
        }
        return $money;
    }






    //老租期整合到新租期表
    public function goodsLease(){

//        die('禁用');

        $start_time=microtime(true);

        $sql="select g.goods_id,s.id as goods_sku_id, if(s.goods_price>0,s.goods_price,g.goods_price) as price,s.goods_official_price,g.goods_tenancy_term from goods as g 
           inner join goods_sku as s ON s.goods_id = g.goods_id where g.goods_tenancy_term<>'' ";

        $goods=DB::select($sql);
        $goods=ob_to_array($goods);

        DB::beginTransaction();
        try{
            foreach ($goods as $k=>$v){
                $ar=[];
                if($v['goods_tenancy_term']){
                    $ar=explode(',',trim($v['goods_tenancy_term'],','));
                }

                if($ar && is_array($ar)){
                    foreach($ar as $vv){
                        $term_num=(int)$vv;
                        $goods_lease_ar=[
                            'goods_id'=>$v['goods_id'],
                            'rent_price'=>$v['price'],
                            'goods_sku_id'=>$v['goods_sku_id'],
                            'term_num'=>$term_num,
                            'deposit_price'=>$v['price'],
                            'buyout_price'=>0
                        ];

                        $channel_cate_goods=ChannelCateGoodsModel::where([['cha_id',1],['goods_id',$v['goods_id']]])->select('cate_id')->first();
                        $channel_cate_goods=ob_to_array($channel_cate_goods);
                        if($channel_cate_goods){
                            $money=$this->buyMoney($channel_cate_goods['cate_id'],$v['goods_official_price'],$v['price']);
                            $goods_lease_ar['buyout_price']=$money;
                        }


                        $goods_lease_id=DB::table('goods_lease')->insertGetId($goods_lease_ar);
                        $goods_lease_term_ar=[];
                        $term_price=number_deal($v['price']/$term_num);
                        for($i=1;$i<=$term_num;$i++){
                            $goods_lease_term_ar[]=[
                                'goods_lease_id'=>$goods_lease_id,
                                'day_num'=>30,
                                'price'=>$term_price
                            ];
                        }
                        $res=DB::table('goods_lease_term')->insert($goods_lease_term_ar);
                    }
                }
            }

            DB::commit();
        }catch (\Exception $e){
            DB::rollBack();
            die($e->getMessage());
        }

         echo '成功。 运行时长：'.(microtime(true)-$start_time);
    }

    // 新增表，字段
    public function sql(){
        $sql="
        新增数据表goods_sku, goods_lease, goods_lease_term, order_group, order_group_bin

            channel_cate_goods 新增字段 sort(排序)

            category 新增字段 cate_note(分类注释)  is_recommend(是否推荐 0不是 ， 1是)";
    }





























}
